1 TREES - TREES - TREES

1.1 Introduction

Sustainability offers a variety of challenges and opportunities in regard to data science. More and more data is available from around the global in regards to sustainability and each regions has its on challenges with sustainability. However, for this project we will focus on a problem that is more local to us. We are going to analyse the impact trees have on our everyday lives in regards to temperature and air quality.

1.2 Goal of the project

We would like to prove that the planting of trees and the development of green spaces in an urban environment have positive correlations with the development of air quality indicators and a negative impact on temperatures in the city of Zurich. This project will try to pinpoint the effect trees have on these indicators. The results will be presented in a way to inform politicians, urban developers as well as the public about the impact that a more green city could have on our everyday lives. While there are a number of potential factors, both local and national/global, can have a significant impact on the climate and for this research we are choosing to focus on data from Zürich in regards to air quality, temperature information and the tree inventory. Further factors are described using sources, but are not included in the data analysis. To clarify our project goal and narrow it down we drew the following picture:

Target Picture

It shows two potential futures for Zürich. One with more green spaces and one with more urban wasteland. The idea of the project is to find out how much the trees and green spaces actually impact the city of Zürich in regard to temperature and air pollution.

1.2.1 Stakeholders

To ensure we include all potential stakeholders in our project at the appropriate level we create the following stakeholder map:

Stakeholders

We sorted them into three possible categories: “direct”, “indirect”, “unintended”. Our main target is to work together with Grüne Stadt Zürich an convince the politicians, the goverment and urban planners on the benefits of a greener city.

1.3 Big Picture: Embedding in the Sustainable Development Goals (SDG)

The SDGs lay out the goals that the global community (United Nations General Assembly in 2015) has agreed on. Total there are 17 goals and they range from 1. No poverty to 17. Partnerships for the goals and include social, economic and environmental targets. For the research we have identified the relevant goals and put them into the three different categories: Risk, Opportunity and Intended. Sustainable Development Goals As can be seen the intended target for our research is to help achieve the goal number 3. Good health and well being. This can be achieved if we can prove a correlation between the amount of trees in a region and an improvement in air quality, which could then lead to increase of planting of new trees in Zürich as well as a reduction of concrete wastelands in favor of green spaces. There are a number opportunities as well in regards to climate action, economic growth and affordable and clean energy. The are only limited risks that this project has. However it is imaginable that the improvement in air quality in certain areas could lead to a increase in inequalities between the rich and poor. It is also important to always be responsible and think of possible unintended consequences while planning for new green zones and planting new trees.

1.4 Data sources

For the research there are three different data sets needed: Air quality information, temperature data, and a tree cadastre. All of these three data sets are publicly available. Following is a quick overview over the different data sets:

1.4.1 Air quality

Format: csv
Number of rows: 283’763
Number of variables: 8 (most important: date, location, type of measurement, value)
Source: https://data.stadt-zuerich.ch/dataset/ugz_luftschadstoffmessung_stundenwerte

1.4.2 Meteo

Format: csv
Number of rows: 4’747
Number of variables: 17 (most important: date, temperature, location)
Source: https://opendata.swiss/de/dataset/taglich-aktualisierte-meteodaten-seit-1992

1.4.3 Tree cadastre

Format: csv
Number of rows: 36’239
Number of variables: 17 (most important: year of planting, diameter of crown, district)
Source: https://data.stadt-zuerich.ch/dataset/geo_baumkataster

1.5 Data preparation

In this step we collected the three data sets and prepared them so that they are for use in the analysis. To achieve this, certain changes to the data were necessary to facilitate an informative analysis.

First we loaded the tree cadastre data and wrangled the data to get additional information such as the amount of trees per year and the number of trees by district.

d.trees <- read.csv('../datasets/gsz.baumkataster_baumstandorte.csv', encoding = "UTF-8")
names(d.trees)[1] <- "objid" 
#head(trees)
#str(trees)


#Wrangling and mutating tree data

# trees by year
tree_ts <- d.trees %>%
  dplyr::select(pflanzjahr) %>%
  filter(pflanzjahr > 1980) %>%
  group_by(pflanzjahr) %>%
  summarise(count = n())

# Trees by district
tree_quartier <- d.trees %>%
  dplyr::select(pflanzjahr, objid, quartier) %>%
  filter(pflanzjahr > 1980) %>%
  group_by(pflanzjahr, quartier) %>%
  summarise(count = n())


# sum of trees
tree_ts_sum <- tree_ts %>%
 mutate(sum_trees = cumsum(count))


# Trees by district, year
tree_quartier_year_full <- d.trees %>%
  dplyr::select(pflanzjahr, quartier, kronendurchmesser) %>%
  filter(pflanzjahr > 1980) %>%
  group_by(pflanzjahr, quartier) %>%
  summarise(count = n(), sum_crown = sum(kronendurchmesser))

# trees by year
tree_year <- d.trees %>%
  dplyr::select(pflanzjahr, kronendurchmesser) %>%
  filter(pflanzjahr > 1980) %>%
  group_by(pflanzjahr) %>%
  summarise(tree_count = n(), crown_sum = sum(kronendurchmesser))

tree_year_sum <- tree_year %>%
  mutate(cum_trees = cumsum(tree_count)) %>%
  mutate(cum_crown = cumsum(crown_sum))

Next we load all the temperature information from the meteo data set. While this data set includes other information such as rain duration we will only use the temperature, as it seems unlikely that the amount of trees significantly impact the rain fall in an area.

filenames.meteo <- list.files(path = "../datasets/meteo/", pattern = "*.csv", full.names = TRUE)
data_list.meteo <- lapply(filenames.meteo, read.csv)
# Combine the data frames in the list into a single data frame
meteo <- do.call(rbind, data_list.meteo)

names(meteo)[1] <- "Date"
meteo$Date <- format(as.Date(meteo$Date), format = "%Y-%m-%d")

# Datensatz mit Temperatur und Globalstrahlung pro Messstation
meteo.extr <- meteo %>%
  filter(Parameter == "T" | Parameter == "StrGlo" | Parameter =="T_max_h1")
meteo.extr$Jahr <- year(meteo.extr$Date)

# weiter mit "normalem" Meteodatensatz
meteo <- meteo[meteo$Standort=="Zch_Stampfenbachstrasse",]
meteo <- meteo %>% 
  select(c("Date","Parameter","Wert")) %>%
  pivot_wider(id_cols = "Date",names_from = "Parameter",values_from = "Wert") %>%
  select(c("Date","T","RainDur","p"))

meteo$Date <- ymd(meteo$Date)

head(meteo)

Lastly the air quality data is loaded. While there are multiple location were air quality was measured not every station measured each variable. They also were not all built at the same time.

filenames.air <- list.files(path = "../datasets/air/", pattern = "*.csv", full.names = TRUE)
data_list.air <- lapply(filenames.air, read.csv)
# Combine the data frames in the list into a single data frame
air <- do.call(rbind, data_list.air)
names(air)[1] <- "Date"
air$Date <- as.Date(format(air$Date), "%Y-%m-%d")
# add column Jahr
air$Jahr <- as.numeric(format(air$Date,'%Y'))
head(air)
air.wide <- air %>% 
  filter(Standort == "Zch_Stampfenbachstrasse") %>%
  select(c("Date", "Parameter", "Wert")) %>%
  pivot_wider(id_cols = "Date", names_from = "Parameter", values_from = "Wert")

air.short <- air[air$Standort=="Zch_Stampfenbachstrasse",]

air.short <- air.short %>%
  select(c("Date", "Parameter", "Wert")) %>%
  pivot_wider(id_cols = "Date",names_from = "Parameter",values_from = "Wert")

keep <- c("Date", "CO", "NOx")
air.short <- air.short[keep]
keep <- c("Date", "CO")
air.co <- air.short[keep]
keep <- c("Date", "NOx")
air.NOx <- air.short[keep]
#air.NOx <- air["NOx"] <- log(air["NOx"])


2 Insights into our datasets


To gain insights from the available information we have created a number of graphical overviews. These already show preliminary results of our analysis.


2.1 Air quality in Zürich and its development over the years


2.1.1 Daily development of Carbon Monoxide and Nitrogen Oxides

Fig. nnn: Daily CO and NOx

Fig. nnn: Daily CO and NOx

A first visual inspection of carbon monoxide and nitrogen oxides as illustrated in figure nnn shows a reduction of both, CO and NOx, as of 1983 up to 2022. The trend line in red shows a significant improvement especially in the Eighties and early Nineties. It is unlikely that trees are the only factor influencing this improvement. Rather other factors such as new laws and regulations play a major role. In addition, in this timeframe, the cars were equipped with catalysts which mainly reduce carbon monoxide, hydrocarbon and nitrogen oxyds. Searching the internet for explanations to which we could refer didn’t lead to any results, as most time series only start as of 1990. Additional potential influencing factors are mentioned in nnn.



–> hier die Referenz auf Loopy Kapitel

The same data depicted as boxplots and summarized per year confirms the improvement of air quaility as well as the trend and the maximum values.

Fig. nnn: CO and NOx per yearFig. nnn: CO and NOx per year

Fig. nnn: CO and NOx per year

 

2.1.2 Difference of Nitrogen Oxides per measuring station


Fig. nnn: Nitrogen oxides per monitoring stationFig. nnn: Nitrogen oxides per monitoring stationFig. nnn: Nitrogen oxides per monitoring station

Fig. nnn: Nitrogen oxides per monitoring station

Nitrogen Oxides have been registered at three different measuring stations. The values differ as the measuring stations have different settings, for example main traffic axis, 6 m distance to the street (Rosengartenstrasse) or moderately frequented road in residential area, 3 m distance to the street (Schimmelstrasse).

Further details on measuring stations:
Stampfenbachstrasse: https://zueriluft.ch/airmo/frontend/station.php?ID=8
Schimmelstrasse: https://zueriluft.ch/airmo/frontend/station.php?ID=6
Rosengartenstrasse: https://zueriluft.ch/airmo/frontend/station.php?ID=11


The analysis done in the following chapters is based on the measurements at Stampfenbachstrasse as this data is going back to 1983.


2.3 Trees in Zürich and their development over the years


Fig. nnn: Year of planting and crown diameterFig. nnn: Year of planting and crown diameterFig. nnn: Year of planting and crown diameter

Fig. nnn: Year of planting and crown diameter

The two graphs in figure nnn on the left side show the numbers of trees planted per year as of 1950. The graph on the left side reflects our data on a yearly basis as we get it from our data set. As in earlier years the number of plantings were mainly summarized every five years, the middle bar chart shows a more consistent view by grouping 10 years into one bar.

Planted trees include both, additional trees and replacements. There were a number of extraordinary environmental influences in recent years, such as cyclone Lothar in December 1999, or the heavy snow fall in January 2021 and storm Bernd in July 2021 which damaged more than 2’000 trees on public ground in such a way that they had to be cut down. Replanting of those 2’000 trees will take time until the year 2025. https://www.stadt-zuerich.ch/ted/de/index/departement/medien/medienmitteilungen/2021/oktober/211007a.html


2.4 Remark on trees on private ground


The City of Zürich is continuously planting trees on public ground as the awareness that trees are an effective measure to reduce heat in the city has become a common understanding. But if we broaden our view and take also the private ground into consideration, we get a different picture. Half of the area of the tree crowns is on private ground and those are disappearing in such a pace, that the overall population of trees is decreasing as well.
(https://www.tagesanzeiger.ch/die-stadt-will-ihre-krone-vergroessern-362720147046)


2.5 Analysis

Having gained an overview of the data, we would now like to analyse the data in more detail using a few different methodologies.

2.5.1 Timeseries Analysis

In this section we are trying to see if there is a correlation over time between the amount of trees and the air quality or temperature. For this we use timeseries analysis, meaning we need to convert our data into timeseries objects. After transforming them we can then look at the decomposition of the objects. Thus we are able to see trends, seasonality and residuals. However the seasonality is only visible for air pollution and temperature, as the tree information is only available on a yearly basis.

meteo %<>% complete(Date=seq.Date(min(Date), max(Date), by='day'))

# Use the time series class of the library stats
freq_daily <- 365.2422
temp <-
  ts(meteo$T, start=c(year(min(meteo$Date)),yday(min(meteo$Date))),
     frequency=freq_daily) %>%
  na_replace(fill=0)

# Stationarity test and decomposition
# adf.test(temp,k=0)
temp_comp=decompose(temp)
plot(temp_comp)
title(sub = "Temperature")

# Manual decomposition
meteo %<>% mutate(year=year(meteo$Date)+yday(meteo$Date)/freq_daily)
temp_trend <- lm(meteo$T~meteo$year)
plot(temp, main="Temperature trend per year")
abline(temp_trend)

# Prepare yearly data
meteo_yr <- meteo %>%
  mutate(temp_raw=replace_na(T,0)) %>%
  group_by(Year=year(Date)) %>%
  filter(Year>=1980 & Year<=2021) %>%
  summarize(temp=mean(temp_raw)) %>%
  ungroup()

temp_yr <- ts(temp, start=min(meteo_yr$Year))
tb_temp_ts <- tbats(temp_yr)

# Differ
## twice-difference the CO2 data
temp_d2 <- diff(temp, differences = 1)
## plot the differenced data
# plot(temp_d2, ylab = "Temperature without trend")

## difference the differenced CO2 data
temp_d2d12 <- diff(temp_d2, lag = 12)
## plot the newly differenced data
plot(temp_d2d12, ylab = "Temperature without trend and seasonality")

Generally we can see that the temperature is slightly rising over time. There is a slight upwards trend noticeable and it is also important to remember how much danger a temperature increase of just one percent can be for the biodiversity.

air.short %<>% complete(Date=seq.Date(min(Date), max(Date), by='day'))

# Use the time series class of the library stats
freq_daily <- 365.2422
co <-
  ts(air.short$CO, start=c(year(min(air.short$Date)),yday(min(air.short$Date))),
     frequency=freq_daily) %>%
  na_replace(fill=0)
NOx <-
  ts(air.short$NOx, start=c(year(min(air.short$Date)), yday(min(air.short$Date))),
     frequency=freq_daily) %>%
  na_replace(fill=0)

# Stationarity test and decomposition
# adf.test(co,k=0)
# adf.test(NOx,k=0)
co_comp=decompose(co)
NOx_comp=decompose(NOx)
# plot(co_comp)
# title(sub = "CO")
# plot(NOx_comp)
# title(sub = "NOx")


# Manual decomposition
air.short %<>% mutate(year=year(air.short$Date)+yday(air.short$Date)/freq_daily)
co_trend <- lm(air.short$CO~air.short$year)
NOx_trend <- lm(air.short$NOx~air.short$year)
# plot(co, main="CO trend per year")
# abline(co_trend)
# plot(NOx, main="NOx trend per year")
# abline(NOx_trend)

air_yr <- air.short %>%
  mutate(co_raw=replace_na(CO,0), NOx_raw=replace_na(NOx,0)) %>%
  group_by(Year=year(Date)) %>%
  filter(Year>=1980 & Year<=2021) %>%
  summarize(co=sum(co_raw), NOx=mean(NOx_raw)) %>%
  ungroup()

# Time series
co_yr <- ts(air_yr$co, start=min(air_yr$Year), frequency=1)
NOx_yr <- ts(air_yr$NOx, start=min(air_yr$Year), frequency=1)

# plot(co_yr)
# plot(NOx_yr)

# Manual decomposition
co_yr_trend <- lm(air_yr$co~air_yr$Year)
plot(co_yr, xlab='Year', ylab='Average co pollution')
abline(co_yr_trend)
title(main = "CO development over the years and trend line")

NOx_yr_trend <- lm(air_yr$NOx~air_yr$Year)
plot(NOx_yr, xlab='Year', ylab='Average NOx pollution')
abline(NOx_yr_trend)
title(main = "NOx development over the years and trend line")

# plot(co_yr)
# pacf(co_yr)
# plot(tbats(co_yr))
# title(sub = "CO ")
tree_ts <-  ts(tree_ts_sum$sum_trees, start=min(tree_ts_sum$pflanzjahr), frequency=1)
tb_tree_ts <- tbats(tree_ts)
tb_co_ts <- tbats(co_yr)
tb_nox_ts <- tbats(NOx_yr)

plot(residuals(tb_co_ts))
title(main = "Residuals of CO")

plot(residuals(tb_nox_ts))
title(main = "Residuals of NOx")

# adf.test(co_yr)

# "Forecast"
# plot(forecast(co_yr))

The timeseries analysis of Carbon monoxide (co) and Nitrogen oxide (NOx) show that the they are closely correlated. It is further clear that they have both decreased drastically since the 1980 in Zürich. It is unlikely that the whole change can be attributed to the growing number of trees and green spaces in Zürich. It is more likely that a variety of factors including advancement in technologies and changes in laws and regulations contributed as well to the improvement in air quality.

# Make the time series comparable
temp_comp_random <- data.frame(Date=time(temp_comp$random), Random=temp_comp$random)
co_comp_random <- data.frame(Date=time(co_comp$random), Random=co_comp$random)
NOx_comp_random <- data.frame(Date=time(NOx_comp$random), Random=NOx_comp$random)
#trees_comp_random <- data.frame(Date=time(trees_comp$random), Random=trees_comp$random)


window <- list(start=1980,end=2021)

temp_comp_random %<>% filter(Date>=window$start&Date<window$end)
co_comp_random %<>% filter(Date>=window$start&Date<window$end)
NOx_comp_random %<>% filter(Date>=window$start&Date<window$end)
#trees_comp_random %<>% filter(Date>=window$start&Date<window$end)

temp_comp_random_ts <- ts(temp_comp_random$Random, start=c(window$start,1), frequency=freq_daily)
co_comp_random_ts <- ts(co_comp_random$Random, start=c(window$start,1), frequency=freq_daily)
NOx_comp_random_ts <- ts(NOx_comp_random$Random, start=c(window$start,1), frequency=freq_daily)
#trees_comp_random_ts <- ts(trees_comp_random$Random, start=c(window$start,1), frequency=freq_daily)


# Cross correlation function
# ccf(temp_comp_random_ts,co_comp_random_ts)

# ccf(temp_comp_random_ts, co_comp_random_ts, 
#     lag.max = 300,
#     main = "Temp - CO Cross-Correlation Plot",
#     ylab = "CCF", 
#     na.action = na.pass)

ccf(residuals(tb_tree_ts), residuals(tb_nox_ts), 
    lag.max = 300,
    main = "Trees - NOx Cross-Correlation Plot",
    ylab = "CCF")

ccf(residuals(tb_tree_ts), residuals(tb_co_ts), 
    lag.max = 300,
    main = "Trees - CO Cross-Correlation Plot",
    ylab = "CCF")

ccf(residuals(tb_tree_ts), residuals(tb_temp_ts), 
    lag.max = 300,
    main = "Trees - Temperature Cross-Correlation Plot",
    ylab = "CCF")

Now one can see the correlations between the sum of trees and air quality (CO and NOx) as well as the correlation between the sum of trees and temperature. The graphs shows no strong correlations, although a slightly significant correlation with a lag is visible for amount of trees on air quality. Meaning that after a some time a larger amount of trees could have an influence in reducing Carbon monoxide and Nitrogen oxide in the air.

2.6 Tableau Dashboards

2.7 Conclusions and findings

2.8 Outlook and next steps

While we did manage to get some very insightful and interesting results, we did also come across some issues, mostly with the data availability and quality. It would be useful to collect more data from across the different districts in Zürich. It would also be helpful if that data would be completely available across a time-frame of at least 20 years. This would allow for creating a more complete picture and better comparisons among districts. Another possibility would be take a closer look at some of the outliers using Extreme Value Theory (EVT). A further approach would be to create a simulation. While this would obviously involve a lot more effort, it would allow to simulate potential scenarios and could thus help city planners and politicians to make more informed policy decisions.